from fastapi import APIRouter
from db.models import *
from fastapi.templating import Jinja2Templates
from fastapi import Request,HTTPException
from pydantic import BaseModel,field_validator
from typing import List
from tortoise.expressions import Q

student_api=APIRouter()


# @student_api.get("/")
# async def get_student():
#     # students = await Student.all()  # 查询所有学生 Queryset类型: [Student(),Student(),Student()]
#     # print("students:",students)
#     # print(students[0].name)
#     # for stu in students:
#     #     print(stu.name,stu.age,stu.sex)
#
#     # 过滤查询,查询班级id为2的学生
#     # students = await Student.filter(clas_id=2)
#     # print("students:",students)
#     # for stu in students:
#     #     print(stu.name, stu.age, stu.sex,stu.sno)
#     # return {"msg":"查询所有学生"}
#     # 过滤查询：查询name为张三的学生信息
#     # students = await Student.filter(name="张三")
#     # print("students:",students)
#     # for stu in students:
#     #     print(stu.id,stu.name,stu.pwd,stu.age,stu.sex,stu.sno,stu.clas_id)
#
#     # 如果查询对象只有一个 使用get方法，返回模型类型对象
#     # stu = await Student.get(id=303) # Student对象
#     # print("stu:",stu) # stu: <Student: Student object (303)>
#     # print(stu.id,stu.name,stu.pwd,stu.age,stu.sex,stu.sno,stu.clas_id)
#
#     # 模糊查询：查询age大于20的学生信息
#     # stu = await Student.filter(age__gt=20) # __gt表示大于 lt表示小于，
#     # # stu = await Student.filter(age__in=[20,22]) # in表示在列表中的值
#     # print("stu",stu)
#     # for stu in stu:
#     #     print(stu.id,stu.name,stu.age,stu.sex,stu.sno,stu.clas_id)
#     # print("查询完毕")
#
#     # values查询
#     # stu = await Student.filter(sno__range=[2001,2003])
#     # # stu = await Student.all().values("name","sno") # 查询所有学生的name字段
#     # print(stu)
#
#     # 一对多查询 多对多查询
#     zhou = await Student.get(name="周杰伦")
#     print(zhou.name)
#     print(zhou.sno)
#     print(await zhou.clas.values("name"))
#     # 查询所有学生的姓名和班级名称
#     student = await Student.all().values("name","clas__name")
#     print(student)
#
#     # 多对多查询
#     # 因为courses没有传递参数 所以显示为一个空列表
#     print(await zhou.courses.all().values("name","teacher__name"))
#
#     return student

# @student_api.get("/")
# async def get_student(page: int = 1, page_size: int = 10):
#     """支持分页的学生查询接口"""
#     skip = (page - 1) * page_size
#     students = await Student.all().offset(skip).limit(page_size)
#     total = await Student.all().count()  # 总条数
#     return {"students": students, "total": total, "page": page, "page_size": page_size}
@student_api.get("/")
async def get_student(page: int = 1, page_size: int = 10):
    """支持分页的学生查询接口，返回班级名称"""
    skip = (page - 1) * page_size

    # 使用values查询，直接获取班级名称
    students = await Student.all().order_by("id").offset(skip).limit(page_size).values(
        "id", "name", "sno", "age", "sex", "pwd", "clas_id", "clas__name"
    )

    # 重命名字段，让前端更容易使用
    student_data = []
    for student in students:
        student_data.append({
            "id": student["id"],
            "name": student["name"],
            "sno": student["sno"],
            "age": student["age"],
            "sex": student["sex"],
            "pwd": student["pwd"],
            "clas_id": student["clas_id"],
            "clas_name": student["clas__name"] or "未知班级"
        })

    total = await Student.all().count()
    return {"students": student_data, "total": total, "page": page, "page_size": page_size}
# 全局搜索功能
@student_api.get("/search")
async def search_students(keyword: str):
    """全局搜索学生接口"""
    # 使用Q对象实现OR条件查询
    students = await Student.filter(
        Q(name__icontains=keyword) | Q(sno__icontains=keyword)
    ).values(
        "id", "name", "sno", "age", "sex", "pwd", "clas_id", "clas__name"
    )

    # 格式化返回数据
    student_data = []
    for student in students:
        student_data.append({
            "id": student["id"],
            "name": student["name"],
            "sno": student["sno"],
            "age": student["age"],
            "sex": student["sex"],
            "pwd": student["pwd"],
            "clas_id": student["clas_id"],
            "clas_name": student["clas__name"] or "未知班级"
        })

    return {"students": student_data}

@student_api.get("/index.html")
async def getAllStudent(request:Request): #request使一个请求对象，request 对象包含了与本次请求相关的所有数据
    templates = Jinja2Templates(directory="templates")
    students = await Student.all() # [Student(),Student(),Student()...]

    return templates.TemplateResponse(
        "index.html",
        {
            "request":request, # 必须要传递request参数 让模板系统能够获取当前请求的上下文信息
            "students":students
        }
    )



# 为添加数据 做一个校验层
class StudentIn(BaseModel):
    name:str
    pwd:str
    age:int
    sex:str
    sno:int
    clas_id:int # 关联班级id
    courses:List[int]=[] # 选的课程id
    # 校验name字段是否是字母
    @field_validator("name")
    def name_must_alpha(cls,value):
            assert value.isalpha(),'name字段出现错误' # 提示name字段出现错误
            return value
    @field_validator("sno")
    def sno_must_alpha(cls,value):
        assert value > 1000 and value < 20000 ,'学号超出范围！' # 提示sno字段出现错误
        return value



@student_api.post("/")
async def add_student(student_in:StudentIn):
    # 插入到数据库中
    # 方式一
   # student = Student(name=student_in.name,pwd=student_in.pwd,age=student_in.age,sex=student_in.sex,sno=student_in.sno,clas_id=student_in.clas_id)
   # await student.save() # 插入到数据库中的student表
   # 方式二
   student = await Student.create(name=student_in.name,pwd=student_in.pwd,age=student_in.age,sex=student_in.sex,sno=student_in.sno,clas_id=student_in.clas_id)
   return student


@student_api.get("/{student_id}")
async def get_student_by_id(student_id:int):
    stu = await Student.get(id = student_id)
    return stu


# 功能报错 找不到数据库连接
@student_api.put("/{student_id}")
async def update_student(student_id:int,student_in:StudentIn):
    # 更新学生信息
    stu = await Student.filter(id=student_id).update(
        name=student_in.name,
        pwd=student_in.pwd,
        sno=student_in.sno,
        age=student_in.age,
        sex=student_in.sex,
        clas_id=student_in.clas_id
    )
    return stu
    return stu


@student_api.delete("/{student_id}")
async def delete_student(student_id:int):
    # 删除学生信息
    deleteCount = await Student.filter(id=student_id).delete()
    if not deleteCount:
        raise HTTPException(status_code=404,detail=f"id为{student_id}的学生数据删除失败")

    return {"msg":"删除成功"}

